﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Ext.Net;
using Hybrid;

namespace DSS.HybridClasses
{
    public static class MaxSubId
    {
        public static Int32 Num { get; set; }
        /// <summary>
        /// получить значение из бд
        /// </summary>
        public static void GetIt()
        {
            var connection = DBConnection.Open();
            if (connection == null) return;
            var command = connection.CreateCommand();
            command.CommandText = "SELECT MAX(SubID) FROM CritValue ";
            //максимум критериев
            Num = (int)command.ExecuteScalar();
            connection.Close();
        }
    }

    public static class CriterionNum
    {
        public static Int32 Num { get; set; }
    }

    public class PreferenceRecord//тут сохрань то, что выбрал пользователь
    {
        public Int32 SubId { get; set; }//номер градации
        public Int32 CriteriaId { get; set; }//ай-ди критерия
        public string Value { get; set; }//значение
        public Int32 PreferenceId { get; set; }//ай-ди предпочтения
        public string Color { get; set; }//цвет
    }
    //таблица с критериями
    public class Grid
    {
        public static DataTable CriteriaDataTable
        {
            get;
            set;
        }

        public static DataTable UserSetsDataTable 
        { 
            get;
            set;
        }


        public static DataTable GetDataTableForComboBox(string parentId, out int maxval)
        {
            maxval = 0;
            var conString = ConfigurationManager.ConnectionStrings["DSSConnectionString"].ConnectionString;
            var con = new SqlConnection(conString);
            con.Open();
            var command = con.CreateCommand();
            const string q = @"SELECT * 
                FROM dbo.crit_scale c_s where c_s.criteria_id=@parentId";
            command.CommandText = q;
            command.Parameters.AddWithValue("@parentId", Convert.ToInt32(parentId));
            var dt = new DataTable();
            dt.Columns.Add("ScaleName");
            dt.Columns.Add("ScaleValue");
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var dr = dt.NewRow();
                var name = reader["name"] as string;
                var rank = Convert.ToDouble((decimal)reader["rank"]);
                dr["ScaleName"] = name;
                dr["ScaleValue"] = rank;
                dt.Rows.Add(dr);
            }
            return dt;
        }

        public static DataTable GetDataTableForCriterias(string paretntId, out int maxval)
        {
            var conString = ConfigurationManager.ConnectionStrings["DSSConnectionString"].ConnectionString;
            var con = new SqlConnection(conString);
            con.Open();
            var command = con.CreateCommand();
            const string q = @"SELECT MAX (cs.ord) 
                FROM (SELECT * FROM dbo.crit_scale c_s where c_s.criteria_id in 
                (SELECT c.id FROM
                dbo.criteria c where c.parent_crit_id=@ParentId)) as cs  ";
            command.CommandText = q;
            command.Parameters.AddWithValue("@ParentId", paretntId);
            var maxValueOrd = (int)command.ExecuteScalar();
            maxval = maxValueOrd;
            CriteriaDataTable = new DataTable();
            CriteriaDataTable.Columns.Add("ID", typeof(int));
            CriteriaDataTable.Columns.Add("Name", typeof(string));
            CriteriaDataTable.Columns.Add("Optimization", typeof(string));
            for (var i = 1; i <= maxValueOrd; ++i)
            {
                CriteriaDataTable.Columns.Add("Value" + i, typeof(string));
            }
            command.CommandText = @" SELECT c.id, c.ismin, c.name, cs.name as value 
                FROM  dbo.criteria c, dbo.crit_scale cs where c.id=cs.criteria_id and c.parent_crit_id=@ParentId
                order by c.ord, cs.ord";
            var reader = command.ExecuteReader();
            int? currentid = null;
            DataRow row = null;
            var ord = 1;
            Criteria cr=null;
            while (reader.Read())
            {
                Gradation gr;
                if ((int)reader["id"] != currentid)
                {
                    ord = 1;
                    row = CriteriaDataTable.NewRow();
                    cr = new Criteria();
                    ListOfCriterias.Add(cr);
                    row["ID"] = currentid = cr.Num = (int)reader["id"];
                    row["Name"] = cr.Name = (string)reader["name"];
                    row["Optimization"] = cr.Optimization =  (int)reader["ismin"] == 1 ? "Min" : "Max";
                    gr = new Gradation {NumInGrid = ord, CriteriaNum = cr.Num};

                    row["Value" + ord] = gr.Value =  (string)reader["Value"];
                    cr.ListOfGradations.Add(gr);
                    ++ord;
                    CriteriaDataTable.Rows.Add(row);
                }
                else
                {
                    if (row == null)
                        throw new Exception("Loading data fail");
                    gr = new Gradation {NumInGrid = ord, CriteriaNum = cr.Num};
                    row["Value" + ord] = gr.Value = (string)reader["Value"];
                    cr.ListOfGradations.Add(gr);
                    ++ord;
                }
            }
            return CriteriaDataTable;
        }















        //   public static Type t { get; set; }//тип для адаптера
        public static List<Criteria> ListOfCriterias = new List<Criteria>();//список критериев
        public static List<PreferenceRecord> Listofpreferences = new List<PreferenceRecord>();//тут сохрань то, что выбрал пользователь
        //добавить клетку в список выбранных
        public static void SaveOnePreference(int criterionid, int subid, int preferenceid, string value, string color)
        {
            if (Listofpreferences.Any(p => (p.Value == value) && (p.CriteriaId == criterionid) && 
                (p.SubId == subid) && (p.PreferenceId == preferenceid) && (p.Color == color)))
            {
                X.Msg.Notify("Error", "Already exists").Show();
                return;
            }
            Listofpreferences.Add(new PreferenceRecord { SubId = subid, CriteriaId = criterionid, 
                PreferenceId = preferenceid, Value = value, Color = color });
        }


        //сохранить все предпочтения пользователя в бд


        public static void SaveAllPrfeferenceInDb(int parId)
        {
            var conString = ConfigurationManager.ConnectionStrings["DSSConnectionString"].ConnectionString;
            var con = new SqlConnection(conString);
            con.Open();
            var command = con.CreateCommand();


            command.CommandText = @"select max(id) from dbo.pref_set_element";


            var elemId = Convert.ToInt32(command.ExecuteScalar());
            //command.CommandText = @"select max(id) from dbo. pref_set";
            command.CommandText = @"DELETE * FROM dbo.preference_set";
          //  var prefIdOffset = Convert.ToInt32(command.ExecuteScalar());//смещение
                var listofpref = new List<int>();//те множества, которые уже были
                foreach (var pr in Listofpreferences)
                {
                  
                    if (!listofpref.Contains(pr.PreferenceId))
                    {
                        listofpref.Add(pr.PreferenceId);
                        command.CommandText = "INSERT INTO dbo.preference_set (id, crit_scale_id, color,rank)" +
                            String.Format("Values ({0}, 'select top 1 cs.id from dbo.crit_scale cs where cs.criteria_id={1} and cs.name={2}', {2},0)", 
                            pr.PreferenceId, parId, pr.Color);
                       
                        command.ExecuteNonQuery();
                    }
                    command.CommandText = "INSERT  INTO dbo.pref_set_element (id, pref_set_id, crit_scale_id) " +
                          String.Format("Values ({0}, {1}, " +
                                        "select top 1 cs.id from dbo.crit_scale cs where cs.criteria_id={2} and cs.ord={3})",
                                        elemId, pr.PreferenceId, pr.CriteriaId, pr.SubId);
                    ++elemId;
                    command.ExecuteNonQuery();
                }
                con.Close();
            
            //TODO
        }

  

        //строка того, что выбрал пользователь
        public static string GetPreferenceString(int id)
        {
            var prefrecs = Listofpreferences.Where(x => x.PreferenceId == id).OrderBy(x=>x.CriteriaId);
            string str="";
         //   int counter = 0;
            int lastCriteriaId=-1;
            foreach (var preferenceRecord in prefrecs)
            {
                if (lastCriteriaId != preferenceRecord.CriteriaId)
                {
                    if (lastCriteriaId != -1)
                    {
                        str += ";";
                        str += "</br>";
                    }
               //     ++counter;
                    lastCriteriaId = preferenceRecord.CriteriaId;
                    str += CriteriaDataTable.Rows[preferenceRecord.CriteriaId]["Name"].ToString();
                    str += "=";
                    str += CriteriaDataTable.Rows[preferenceRecord.CriteriaId]
                        ["Value" + preferenceRecord.SubId].ToString();
                }
                else
                {
                //    ++counter;
                    str += " or ";
                    str += CriteriaDataTable.Rows[preferenceRecord.CriteriaId]
                        ["Value" + preferenceRecord.SubId].ToString();
                }
               // if (counter%2 == 0)
               //     str += "</br>";
            }
            return str;



            /*return prefrecs.Aggregate("", (current, pr) => 
                current + (CriteriaDataTable.Rows[pr.CriteriaId]["Name"].ToString() + "=" +
                CriteriaDataTable.Rows[pr.CriteriaId]["Value" + pr.SubId].ToString()));
            */
        }


        public static void LoadPreferences()
        {
            var connection = DBConnection.Open();
            if (connection == null) return;
            var command = connection.CreateCommand();
            command.CommandText = @"SELECT pv.PreferenceID, pv.CritID, pv.SubID, p.ID, p.Color, cv.Value
                FROM Preferences p, PreferenceValues pv, CritValue cv 
                WHERE pv.PreferenceID=p.ID AND (cv.SubID)=pv.SubID AND (cv.IDCriterion)=pv.CritID";
            var sdr = command.ExecuteReader();
            while (sdr.Read())
            {
                var pr = new PreferenceRecord
                {
                    SubId = sdr.GetInt32(2),
                    CriteriaId = sdr.GetInt32(1),
                    PreferenceId = sdr.GetInt32(0),
                    Color = sdr.GetString(4),
                    Value = sdr.GetString(5)
                };
                Listofpreferences.Add(pr);
            }
            sdr.Close();
            connection.Close();
        }
        //проверка на то, чтобы в каждом множестве были вссе критерии
        public static string CheckUserPreferences()
        {
            var str = "";
            try
            {
                var alreadyCheckedPreferences = new List<int>();

                var checkedcrit = new Dictionary<int, bool>();
                for (byte i = 0; i < CriterionNum.Num; ++i)
                {
                    checkedcrit.Add(i, false);
                }
                for (var i = 0; i < Listofpreferences.Count; ++i)
                {
                    if (!alreadyCheckedPreferences.Contains(Listofpreferences[i].PreferenceId))
                    {
                        alreadyCheckedPreferences.Add(Listofpreferences[i].PreferenceId);
                        for (var j = i; j < Listofpreferences.Count; ++j)
                        {
                            if ((Listofpreferences[i].PreferenceId == Listofpreferences[j].PreferenceId)
                                && (!checkedcrit[Listofpreferences[j].CriteriaId]))
                            {
                                checkedcrit[Listofpreferences[j].CriteriaId] = true;
                            }
                        }

                        for (var k = 0; k < checkedcrit.Count; ++k)
                            if (!checkedcrit[k])
                            {
                                str = str + "Prefrence:" + Listofpreferences[i].PreferenceId + "-Criterion" + k;
                            }
                        for (var k = 0; k < checkedcrit.Count; ++k)
                        {
                            checkedcrit[k] = false;
                        }

                    }
                    else
                        continue;
                }
            }
            catch (Exception)
            {
            }
            finally
            {
               
            }
             return str;
        }
    }
}
